Example and Exceution store procedure
Course- SQL Sever Store Procedure >
Syntax:
CREATE
PROCEDURE dbo.StoredProcedureName
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
AS
/* Place your Query here */
RETURN
Example 1:
Create PROCEDURE dbo.GetAllEmployees
As
Select * from Employee
Execution query :
exec GetAllEmployees
Example 2:
ALTER PROCEDURE dbo.GetEmployee
@empid int
As
Select * from Employee where Employee.EmployeeID =@empid
Execution Query :
exec dbo.GetEmployee 1
Example 3 :
Create PROCEDURE dbo.UpdateEmpNationalID
(
@empid int,@newcontactid int,@oldcontactno int OUTPUT
)
AS
Select @oldcontactno =[ContactID] FROM [dbo].[Employee] where [Employee].EmployeeID=@empid
if
(
@oldcontactno=@newcontactid
)
begin
RETURN 0 end else
begin
update Employee set ContactID = @newcontactid where Employee.EmployeeID =@empid
return 1
end
Execution Query:
Declare @oldcontactno int
Declare @returnvalue int
exec @returnvalue =UpdateEmpNationalID 1,123,@oldcontactno output
Select @oldcontactno 'Old Contact'
Select @returnvalue 'Returned value